/**
 * fileuploadanddownload
 * DaoUtil.java
 * @author 刘永杰
 * date 2011-9-17
 * @version 1.0
 */
package com.lyjilu.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 刘永杰
 * date 2011-9-17
 * @version 1.0
 * 封装部分DAO操作，简化代码
 */
public class DaoUtil {
//	private static final String DRIVERCLASS = "oracle.jdbc.driver.OracleDriver";
//	private static final String URL = "jdbc:oracle:thin:@192.168.1.110:1521:expbook";
//	private static final String USER = "sec_race";
//	private static final String PASSWORD = "race";
	public static final String METADATANAME = "__MetaData";
	public static final String DBDATANAME = "__DBDataName";
	
	private static final String MYSQLDRIVERCLASS = "com.mysql.jdbc.Driver";
	private static final String MYSQLURL = "jdbc:mysql://localhost:3306/stu";
	private static final String MYSQLUSER = "root";
	private static final String MYSQLPASSWORD = "root";

	/**
	 * 关闭数据库资源
	 * 
	 * @param rs
	 *            ResultSet, 如果没有就传NULL
	 * @param ps
	 *            PreparedStatement, 如果没有就传NULL
	 * @param conn
	 *            Connection, 如果没有就传NULL
	 */
	public static void closeDao(ResultSet rs, PreparedStatement ps,
			Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				rs = null;
			}
		}
		if (ps != null)
			try {
				ps.close();
			} catch (SQLException e) {
				ps = null;
			}
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				conn = null;
				e.printStackTrace();
			}
	}

	/**
	 * 取得未来指定天数的日期字符串
	 * 
	 * @param num
	 *            未来的天数
	 * @param curr
	 *            要查询的日期
	 * @param formateStr
	 *            格式字符串，必须符合curr格式，否则将出错
	 * @return list 封装了未来指定天数的集合
	 * @throws ParseException
	 */
	public static List<String> getNextDays(int num, String curr,
			String formateStr) throws ParseException {
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat formate = new SimpleDateFormat(formateStr);
		Date date = null;
		long d = 0;
		date = formate.parse(curr);
		d = date.getTime();
		cal.setTimeInMillis(d);
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < num; i++) {
			if (i != 0)
				cal.add(Calendar.DATE, 1);
			list.add(cal.get(Calendar.YEAR) + "-"
					+ (cal.get(Calendar.MONTH) + 1) + "-"
					+ cal.get(Calendar.DAY_OF_MONTH));
		}
		return list;
	}

	/**
	 * 取得传递过来的Sql的查询总数的sql;
	 * 
	 * @param sql
	 *            StringBuffer对象，装有查询的sql
	 * @return 封装好的查询结果总数的StringBuffer sql
	 */
	public static StringBuffer getCountSql(StringBuffer sql) {
		StringBuffer sbr = new StringBuffer();
		sbr.append(" select count(*) from (");
		sbr.append(sql);
		sbr.append(" )");
		return sbr;
	}

	public static StringBuffer getLimitSql(StringBuffer sql) {
		return getLimitSql(sql, 0, 0);
	}

	/**
	 * 取得分页查询sql
	 * 
	 * @param sql
	 *            StringBuffer对象，装有查询的sql
	 * @param size
	 *            每页显示数据条数
	 * @param beginItem
	 *            开始项
	 * @return 封装好的分页查询的StringBuffer sql
	 */
	public static StringBuffer getLimitSql(StringBuffer sql, int size,
			int beginItem) {
		StringBuffer sbr = new StringBuffer();
		int from = 0;
		int end = 0;
		if (size > 0) {
			from = beginItem;
			end = beginItem + size;
		} else {
			from = 0;
		}
		sbr.append(" select * from ( select row_.*, rownum rownum_ from (");
		sbr.append(sql);
		sbr.append(" ) row_");
		if (end != 0)
			sbr.append(" where rownum <= " + end);
		sbr.append(" ) where rownum_ > " + from);
		return sbr;
	}

	/**
	 * 判断某字符串是否为空字符串或为null
	 * 
	 * @param str
	 *            要判断的字符串
	 * @return 判断结果
	 */
	public static boolean isne(String str) {
		return str == null || "".equals(str);
	}

	/**
	 * 格式字符串，通过传递过来的日期格式
	 * 
	 * @param date
	 *            日期
	 * @param formateStr
	 *            格式字符串
	 * @return 格式化后的字符串
	 */
	public static String formateDateToString(Date date, String formateStr) {
		SimpleDateFormat formate = new SimpleDateFormat(formateStr);
		return formate.format(date);
	}

	public static Date formateStringToDate(String dateStr, String formateStr)
			throws ParseException {
		SimpleDateFormat formate = new SimpleDateFormat(formateStr);
		return formate.parse(dateStr);
	}

	/**
	 * 通过指定sequence，取得sequence下一个值
	 * 
	 * @param seq
	 *            要查询的sequence
	 * @return seq的下一个值
	 */
	public static String getSeqBySeqName(String seq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = null;
		String sql = "select " + seq + ".nextval as id from dual";
		try {
			conn = DaoUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs != null && rs.next()) {
				result = rs.getString("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoUtil.closeDao(rs, ps, conn);
		}
		return result;
	}

	/**
	 * 
	 * @param seq
	 * @param Ds
	 *            要查询seq的数据源
	 * @return 得到下一个序列值
	 */
	public static String getSeqNextBySeqName(String seq, String Ds) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = null;
		String sql = "select " + seq + ".nextval as id from dual";
		try {
			conn = DaoUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs != null && rs.next()) {
				result = rs.getString("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoUtil.closeDao(rs, ps, conn);
		}
		return result;
	}

	/**
	 * 获取系统配置，通过产品编号及code
	 * 
	 * @param system_product_code
	 *            产品编号
	 * @param code
	 *            要查询的编码
	 * 
	 * @return 配置值
	 */
	public static String getSysConfig(String system_product_code, String code) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = null;
		String sql = "select * from system_config s where ";
		if (system_product_code == null) {
			sql += " s.system_product_code is null and code = '" + code + "'";
		} else
			sql += " s.system_product_code = '" + system_product_code
					+ "' and code = '" + code + "'";
		try {
			conn = DaoUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs != null && rs.next()) {
				result = rs.getString("value");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoUtil.closeDao(rs, ps, conn);
		}
		return result;
	}

	
	public static Connection getConn() throws Exception {
		Connection con = null;
		try {
			Class.forName(MYSQLDRIVERCLASS);
			con = DriverManager.getConnection(MYSQLURL, MYSQLUSER,
					MYSQLPASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	/**
	 * 通过列明，取得列值，
	 * @param rs
	 * @param columnName
	 * @param type
	 * @return 取得的对象
	 * @throws SQLException
	 */
	public static Object getObjectByName(ResultSet rs, String columnName,
			int type) throws SQLException {
		Object obj = null;
		switch (type) {
		case Types.VARCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.NVARCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.NUMERIC:
			obj = rs.getBigDecimal(columnName);
			break;
		case Types.BLOB:
			obj = rs.getBlob(columnName);
			break;
		case Types.LONGVARBINARY:
			obj = rs.getBlob(columnName);
			break;
		case Types.NCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.TIME:
			obj = rs.getDate(columnName);
			break;
		case Types.TIMESTAMP:
			obj = rs.getTimestamp(columnName);
			break;
		case Types.FLOAT:
			obj = rs.getFloat(columnName);
			break;
		case Types.DOUBLE:
			obj = rs.getDouble(columnName);
			break;
		case Types.INTEGER:
			obj = rs.getInt(columnName);
			break;
		default:
			obj = rs.getString(columnName);
			break;
		}
		return obj;
	}

	/**
	 * 查询指定sql，取得查询结果，并返回
	 * @param conn
	 *            数据库连接
	 * @param sql
	 *            要查询的sql
	 * @throws Exception
	 */
	public static Map<String, Object> find(Connection conn, String sql,
			Object... objs) {
//		long t1 = System.currentTimeMillis();
		ResultSet rs = null;
		PreparedStatement ps = null;
		Map<String, Object> map = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				Object o = objs[i];
				if (o instanceof String)
					ps.setString(i + 1, String.valueOf(o));
				else if (o instanceof java.sql.Date) {
					ps.setDate(i + 1, (java.sql.Date) o);
				} else if (o instanceof Integer) {
					ps.setInt(i + 1, Integer.valueOf(o + ""));
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int numberOfColumns = rsmd.getColumnCount();
			List<String> metaData = new ArrayList<String>();
			map = new HashMap<String, Object>();
			for (int i = 0; i < numberOfColumns; i++) {
				metaData.add(rsmd.getColumnLabel(i + 1));
			}
			map.put(METADATANAME, metaData);
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> row = null;
			while (rs.next()) {
				row = new HashMap<String, Object>();
				for (int i = 0; i < numberOfColumns; i++) {
//					System.out.println(rsmd.getColumnType(i + 1) + "|"
//							+ rsmd.getColumnTypeName(i + 1));
					row.put(rsmd.getColumnLabel(i + 1),
							DaoUtil.getObjectByName(rs,
									rsmd.getColumnLabel(i + 1),
									rsmd.getColumnType(i + 1)));
				}
				list.add(row);
			}
			map.put(DBDATANAME, list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoUtil.closeDao(rs, ps, conn);
		}
//		long t2 = System.currentTimeMillis();
		// System.out.println(t2 - t1);
		return map;

	}
}
